package com.tim.dao.Impl;

import com.tim.dao.HouseDao;
import com.tim.pojo.House;
import com.tim.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 作者：Tim
 * 时间：2021/4/26 17:42
 */
public class HouseDaoImpl implements HouseDao {
    @Override
    public List<House> selectAll() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from house";
        return qr.query(sql,new BeanListHandler<House>(House.class));
    }

    @Override
    public int deleteHouse(int houseID) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "delete from house where houseID = ?";
        Object[] args ={houseID};
        return qr.update(sql,args);
    }

    @Override
    public int addHouse(House house) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "insert into house (houseID,housePlot,houseBuild,houseNum,houseName,housePeople,houseTel,roomNum,houseCell,houseLevel,houseDetail,checkinTime) values (?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] args = {house.getHouseID(), house.getHousePlot(),house.getHouseBuild(),house.getHouseNum(),house.getHouseName(),house.getHousePeople(),house.getHouseTel(),house.getRoomNum(),house.getHouseCell(),house.getHouseLevel(),house.getHouseDetail(),house.getCheckinTime()};
        return qr.update(sql,args);
    }

    @Override
    public House selectOne(int houseID) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from house where houseID = ?";
        Object[] args = {houseID};
        return qr.query(sql,new BeanHandler<House>(House.class),args);
    }

    @Override
    public int updateHouse(House house) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql ="update house set housePlot=?,houseBuild=?,houseNum=?,houseName=?,housePeople=?,houseTel=?,roomNum=?,houseCell=?,houseLevel=?,houseDetail=?,checkinTime=? where houseID = ?";
        Object[] args ={house.getHousePlot(),house.getHouseBuild(),house.getHouseNum(),house.getHouseName(),house.getHousePeople(),house.getHouseTel(),house.getRoomNum(),house.getHouseCell(),house.getHouseLevel(),house.getHouseDetail(),house.getCheckinTime(),house.getHouseID()};
        return  qr.update(sql,args);
    }

    @Override
    public List<House> limit(Integer currentPage, Integer pageSize, String houseName) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(houseName==null){
            String sql ="select * from house limit ?,?";
            Object[] args = {(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<House>(House.class),args);
        }else{
            String sql ="select * from house where houseName = ? limit ?,?";
            Object[] args = {"%"+houseName+"%",(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<House>(House.class),args);
        }
    }

    @Override
    public int count(String houseName) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(houseName == null){
            String sql = "select count(*) from house";
            return qr.query(sql,new ScalarHandler<Number>()).intValue();
        }else{
            String sql = "select count(*) from house where houseName like ?";
            return qr.query(sql,new ScalarHandler<Number>(),"%"+houseName+"%").intValue();
        }
    }
}
